﻿<%@ WebHandler Language="C#" Class="SyncTableInfoHandler" %>

using System;
using System.Web;
using BusinessRule.DTO;
using System.Web.SessionState;
using DatabaseAccess.DBUtility;
using System.Data.OracleClient;
using System.Data;
using System.Linq;
using System.Collections.Generic;
using System.Collections;
using Newtonsoft.Json;
using BusinessRule.Business;
using BusinessRule.IBusiness;
using BusinessRule.BusinessFactory;

//Description:同步表处理程序
//Copyright (c) : 通力凯顿（北京）系统集成有限公司
//Writer:longb
//create Date:2024-01-10

public class SyncTableInfoHandler : IHttpHandler, IRequiresSessionState
{
    CurrentUserInfo m_UserInfo;
    public void ProcessRequest(HttpContext context)
    {
        try
        {
            var action = context.Request["action"].ToString();
            m_UserInfo = (CurrentUserInfo)context.Session["CurrentUserInfo"];
            switch (action)
            {
                case "getSyncTableInfoOrTask": //获取待同步表数据
                    getSyncTableInfoOrTask(context);
                    break;
                case "saveSyncTableInfo": //保存待同步表数据
                    saveSyncTableInfo(context);
                    break;
                case "deleteSyncTableInfo": //删除待同步表数据
                    deleteSyncTableInfo(context);
                    break;
                case "saveSyncTableTask": //保存待同步任务数据
                    saveSyncTableTask(context);
                    break;
                case "deleteSyncTableTask": //删除待同步任务数据
                    deleteSyncTableTask(context);
                    break;
                case "getSyncTableChangeRecord"://获取表数据推送记录
                    getSyncTableChangeRecord(context);
                    break;
                case"sendAginSyncTableChangeRecord"://表数据推送记录重推
                    sendAginSyncTableChangeRecord(context);
                    break;
                case"saveSynSendData":
                    saveSynSendData(context);
                    break;
                case "deleteSynSendData":
                    deleteSynSendData(context);
                    break;
                case"InitPageData":
                    InitPageData(context);
                    break;
                case "getSyncTablePushRecord"://查询推送产线记录
                    getSyncTablePushRecord(context);
                    break;
                case "getSyncTablePushRecordDetail"://查询推送产线记录--子关联明细表
                    getSyncTablePushRecordDetail(context);
                    break;
                case "sendAginRecord"://推送产线记录---修改重推
                    sendAginRecord(context);
                    break; 
                case"sendAginEmployeeData"://推送产线记录---人员信息重推 
                    sendAginEmployeeData(context);
                    break;           
                default:
                    break;
            }
        }
        catch (Exception ex)
        {
            var result = new uMESProcessResult();
            result.Result = false;
            result.Message = ex.Message;
            context.Response.ContentType = "text/html";
            context.Response.Write(JsonConvert.SerializeObject(result));
        }
    }
 
    public bool IsReusable {
        get {
            return false;
        }
    }

    private void getSyncTableInfoOrTask(HttpContext context)
    {
        string tablename = context.Request["tablename"].ToString();
        string pageSize = context.Request["pageSize"].ToString();
        string pageNumber = context.Request["pageNumber"].ToString();
        string selectTable = context.Request["selectTable"].ToString();
        string selectSQL = @"SELECT *FROM {0} t where 1=1 ";//sync_TableInfo 
        if (!string.IsNullOrWhiteSpace(tablename))
        {
            selectSQL += " and TABLENAME = '" + tablename + "'";
        }
        if (!string.IsNullOrWhiteSpace(context.Request["data"].ToString())) 
        {
            var dic = JsonConvert.DeserializeObject<Dictionary<string, string>>(context.Request["data"].ToString());
            if (!string.IsNullOrWhiteSpace(dic["toappid"])) { selectSQL += " and EXISTS (select 1 from sync_sendtoappid s where s.parentid=t.id and s.toappid like '%" + dic["toappid"] + "%')"; }
            if (!string.IsNullOrWhiteSpace(dic["startDate"])) { selectSQL += " and to_char(createTime,'yyyy-MM-dd hh24:mi:ss')>='" + dic["startDate"] + "'"; }
            if (!string.IsNullOrWhiteSpace(dic["endDate"])) { selectSQL += " and to_char(createTime,'yyyy-MM-dd hh24:mi:ss')<='" + dic["endDate"] + "'"; }
        }
        selectSQL += "order by createtime DESC";
        var paraPage = new uMESPagingDataDTO();
        paraPage.CurrentPageIndex = Convert.ToInt32(pageNumber);
        paraPage.PageSize = Convert.ToInt32(pageSize);
        paraPage.strSQL = string.Format(selectSQL, selectTable);
        uMESProcessResult result = DatabaseHelper.RunProcedureForPage(paraPage);
        context.Response.Write(JsonConvert.SerializeObject(result));
    }

    private void saveSyncTableInfo(HttpContext context)
    {
        DataTable data = JsonConvert.DeserializeObject<DataTable>(context.Request["data"].ToString());
        var insertSQL = @"insert into sync_TableInfo (ID,tablename,uniquecolname,sync_interval,nextsynctime,lastsynctime,createtime)values('{0}','{1}','{2}','{3}',to_date('{4}','yyyy-MM-dd hh24:mi:ss'),to_date('{5}','yyyy-MM-dd hh24:mi:ss'),sysdate)";
        var TRIGGER = @"BEGIN CREATE_SYNC_TRIGGER('{0}','{1}'); end;";//创建触发器
        var arrList = new ArrayList();
        var existMSG = string.Empty;
        foreach (DataRow row in data.Rows)
        {
            if (string.IsNullOrWhiteSpace(row["tablename"].ToString()) || string.IsNullOrWhiteSpace(row["uniquecolname"].ToString())) { continue; }
            if (string.IsNullOrWhiteSpace(row["ID"].ToString()))
            {
                arrList.Add(string.Format(insertSQL, Guid.NewGuid(), row["tablename"].ToString().ToUpper(), row["uniquecolname"].ToString().ToUpper(), row["sync_interval"], row["nextsynctime"], row["lastsynctime"]));
            }
            else
            {
                arrList.Add(string.Format("update sync_TableInfo set tablename='{0}',uniquecolname='{1}',sync_interval='{2}',nextsynctime=to_date('{3}','yyyy-MM-dd hh24:mi:ss'),lastsynctime=to_date('{4}','yyyy-MM-dd hh24:mi:ss'),lastchangetime=sysdate where ID='{5}'", row["tablename"].ToString().ToUpper(), row["uniquecolname"].ToString().ToUpper(), row["sync_interval"], row["nextsynctime"], row["lastsynctime"], row["id"]));
            }

            var existTableName = @"select COUNT(*) CN from user_tables where table_name ='"+row["tablename"]+"'";
            var existUniqueColname = @"select count(*) CN from user_tab_columns where table_name = '" + row["tablename"] + "' and column_name = '" + row["uniquecolname"] + "'";
            if (DatabaseHelper.Query_ReturnDatTable(existTableName).Rows[0]["CN"].ToString() != "0" && DatabaseHelper.Query_ReturnDatTable(existUniqueColname).Rows[0]["CN"].ToString() != "0")
            {
                //如果表存在，列存在，创建触发器
                arrList.Add(string.Format(TRIGGER, row["tablename"].ToString().ToUpper(), row["uniquecolname"].ToString().ToUpper()));
            }
            else {
                existMSG += row["tablename"]+",";
            }
        }
        uMESProcessResult result = new uMESProcessResult();
        if (arrList.Count > 0)
        {
            result.Result = DatabaseHelper.ExecuteSqlListTran(arrList);
        }
        result.Message = result.Result ? "保存成功" : "保存失败";
        if (!string.IsNullOrWhiteSpace(existMSG)) { result.Message += " 【" + existMSG.TrimEnd(',')+"】表或列不存在"; }
        context.Response.Write(JsonConvert.SerializeObject(result));
    }

    private void deleteSyncTableInfo(HttpContext context)
    {
        DataTable data = JsonConvert.DeserializeObject<DataTable>(context.Request["data"].ToString());
        var arrList = new ArrayList();
        foreach (DataRow row in data.Rows)
        {
            arrList.Add(string.Format("delete from sync_TableInfo  where ID='{0}'", row["ID"]));
           
            var existTableName = @"select COUNT(*) CN from user_tables where table_name ='" + row["tablename"] + "'";
            if (DatabaseHelper.Query_ReturnDatTable(existTableName).Rows[0]["CN"].ToString() != "0")
            {
                //如果表存在，删除触发器
                arrList.Add(string.Format("drop trigger S_{0}", row["tablename"].ToString().ToUpper()));
            }
        }
        uMESProcessResult result = new uMESProcessResult();
        if (arrList.Count > 0)
        {
            result.Result = DatabaseHelper.ExecuteSqlListTran(arrList);
        }
        result.Message = result.Result ? "删除成功" : "删除失败";
        context.Response.Write(JsonConvert.SerializeObject(result));
    }

    private void saveSyncTableTask(HttpContext context)
    {
        DataTable data = JsonConvert.DeserializeObject<DataTable>(context.Request["data"].ToString());
        var insertSQL = @"insert into sync_TableTask (ID,tablename,OperateType,createtime)values('{0}','{1}','{2}',sysdate)";
        var arrList = new ArrayList();
        foreach (DataRow row in data.Rows)
        {
            if (string.IsNullOrWhiteSpace(row["ID"].ToString()))
            {
                arrList.Add(string.Format(insertSQL, Guid.NewGuid(), row["tablename"].ToString().ToUpper(), row["OperateType"]));

            }
            else
            {
                arrList.Add(string.Format("update sync_TableTask set tablename='{0}',OperateType='{1}',lastchangetime=sysdate where ID='{2}'", row["tablename"].ToString().ToUpper(), row["OperateType"].ToString(), row["id"]));
            }
        }
        uMESProcessResult result = new uMESProcessResult();
        if (arrList.Count > 0)
        {
            result.Result = DatabaseHelper.ExecuteSqlListTran(arrList);
        }
        result.Message = result.Result ? "保存成功" : "保存失败";
        context.Response.Write(JsonConvert.SerializeObject(result));
    }

    private void deleteSyncTableTask(HttpContext context)
    {
        DataTable data = JsonConvert.DeserializeObject<DataTable>(context.Request["data"].ToString());
        var arrList = new ArrayList();
        foreach (DataRow row in data.Rows)
        {
            arrList.Add(string.Format("delete from sync_TableTask  where ID='{0}'", row["ID"]));
        }
        uMESProcessResult result = new uMESProcessResult();
        if (arrList.Count > 0)
        {
            result.Result = DatabaseHelper.ExecuteSqlListTran(arrList);
        }
        result.Message = result.Result ? "删除成功" : "删除失败";
        context.Response.Write(JsonConvert.SerializeObject(result));
    }

    private void getSyncTableChangeRecord(HttpContext context) 
    {
        Dictionary<string, string> dic = JsonConvert.DeserializeObject<Dictionary<string, string>>(context.Request["data"].ToString());
        var tbdefSQL = @"select distinct t.column_name,s.data_type, t.comments,to_char(s.column_id) column_id,nullable,s.data_length,s.char_col_decl_length,
                        	    case when s.data_type='NVARCHAR2' or s.data_type='NCHAR' then to_char(s.char_col_decl_length) else to_char(s.data_length) end data_length,
                                case t.column_name  when(select col.column_name from user_constraints con,user_cons_columns col
									where con.constraint_name=col.constraint_name and con.constraint_type='P'
									and col.table_name='{0}') then '1'else '0' end as iskey
                                from user_col_comments t, user_tab_columns s 
                                where t.table_name ='{0}' and s.table_name ='{0}'
                                and t.column_name = s.column_name
                                order by TO_NUMBER(s.column_id)";
        var tableDef = DatabaseHelper.Query_ReturnDatTable(string.Format(tbdefSQL, dic["TABLENAME"].ToString()));
        var dt = new DataTable();
        if (dic.ContainsKey("TYPE")) {
            dt = tableDef;
            var dt3 = tableDef.Clone();
             string[] columns = tableDef.Columns.Cast<DataColumn>()
                                       .Select(column => column.ColumnName)
                                       .ToArray();
            foreach (string column in columns)
            {
                DataRow newrow = dt3.NewRow();
                newrow["COLUMN_NAME"] = column;
                dt3.Rows.Add(newrow);
            }
            tableDef = dt3;
        }
        else
        {
            var sql = @"select * from {0} where {1}='{2}'";
            dt = DatabaseHelper.Query_ReturnDatTable(string.Format(sql, dic["TABLENAME"], dic["UNIQUECOLNAME"], dic["UNIQUECOLVALUE"]));
        }
        var sql2 = @"select * from sync_sendtoappid ss where ss.parentid='{0}'";
        var dt2 = DatabaseHelper.Query_ReturnDatTable(string.Format(sql2, dic["ID"]));
        dt2.TableName = "sendtoappid";
        var dst = new DataSet();
        dst.Tables.Add(dt2);
        uMESProcessResult result = new uMESProcessResult();
        result.DBTable = tableDef;
        result.ReturnData = dt;
        result.DBDataSet = dst;
        result.Result = true;
        context.Response.Write(JsonConvert.SerializeObject(result));
    }

    private void sendAginSyncTableChangeRecord(HttpContext context) 
    {
        var upsql = @"update sync_sendtoappid set ismeshandle=0 where ID='{0}' and ismeshandle=1";
        var upsql2 = @"update sync_sendtoappid set ismeshandle=0 where PARENTID='{0}' and ismeshandle=1";
        if (!string.IsNullOrWhiteSpace(context.Request["toappid"].ToString())) {
            upsql2 += " and toappid like '%" + context.Request["toappid"].ToString() + "%'";
        }
        var arr=new ArrayList();
        if (context.Request["type"].ToString().Equals("0")) {//单个重推
            var dic=JsonConvert.DeserializeObject<Dictionary<string,string>>(context.Request["data"].ToString());
            arr.Add(string.Format(upsql, dic["ID"]));
        } else { //批量重推
            var dt = JsonConvert.DeserializeObject<DataTable>(context.Request["data"].ToString());
            foreach(DataRow row in dt.Rows)
            {
                arr.Add(string.Format(upsql2, row["ID"]));
            }
        }
        uMESProcessResult result = new uMESProcessResult();
        result.Result = DatabaseHelper.ExecuteSqlListTran(arr);
        result.Message = result.Result ? "操作成功" : "操作失败";
        context.Response.Write(JsonConvert.SerializeObject(result));
    }

    private void saveSynSendData(HttpContext context)
    {
        DataTable data = JsonConvert.DeserializeObject<DataTable>(context.Request["data"].ToString());
        var parentid = context.Request["parentid"].ToString();
        var insertSQL = @"insert into sync_sendtoappid (ID,parentid,toappid)values('{0}','{1}','{2}')";
        var arrList = new ArrayList();
        foreach (DataRow row in data.Rows)
        {
            if (string.IsNullOrWhiteSpace(row["ID"].ToString()))
            {
                arrList.Add(string.Format(insertSQL, Guid.NewGuid(), parentid, row["toappid"].ToString()));

            }
            else
            {
                arrList.Add(string.Format("update sync_sendtoappid set toappid='{0}' where ID='{1}'", row["toappid"].ToString(),row["id"]));
            }
        }
        uMESProcessResult result = new uMESProcessResult();
        if (arrList.Count > 0)
        {
            result.Result = DatabaseHelper.ExecuteSqlListTran(arrList);
        }
        result.Message = result.Result ? "保存成功" : "保存失败";
        context.Response.Write(JsonConvert.SerializeObject(result));
    }

    private void deleteSynSendData(HttpContext context)
    {
        DataTable data = JsonConvert.DeserializeObject<DataTable>(context.Request["data"].ToString());
        var arrList = new ArrayList();
        foreach (DataRow row in data.Rows)
        {
            arrList.Add(string.Format("delete from sync_SendToappid  where ID='{0}'", row["ID"]));
        }
        uMESProcessResult result = new uMESProcessResult();
        if (arrList.Count > 0)
        {
            result.Result = DatabaseHelper.ExecuteSqlListTran(arrList);
        }
        result.Message = result.Result ? "删除成功" : "删除失败";
        context.Response.Write(JsonConvert.SerializeObject(result));
    }

    private void InitPageData(HttpContext context) 
    {
        uMESProcessResult result = new uMESProcessResult();
        result.DBTable = DatabaseHelper.Query_ReturnDatTable("select distinct st.tablename from sync_tableinfo st order by st.tablename");
        result.ReturnData = DatabaseHelper.Query_ReturnDatTable("select distinct st.toappid from sync_sendtoappid st");
        context.Response.Write(JsonConvert.SerializeObject(result));
    }

    /// <summary>
    /// 查询推送产线记录
    /// </summary>
    /// <param name="context"></param>
    private void getSyncTablePushRecord(HttpContext context) 
    {
        var para = JsonConvert.DeserializeObject<Dictionary<string,string>>(context.Request["data"].ToString());
        string pageSize = context.Request["pageSize"].ToString();
        string pageNumber = context.Request["pageNumber"].ToString();
        var selectSQL = @"SELECT cci.MFGORDERNAME,cci.MFGORDERID,cci.WORKFLOWID,cci.WORKFLOWNAME||':'||cci.WORKFLOWREVISION WORKFLOWINFO,
            cci.PRODUCTNAME||':'||cci.PRODUCTREVISION PRODUCTINFO,cci.BOMID,
            cci.CONTAINERNAME,cci.CONTAINERID,cci.FACTORYNAME,ss.TOAPPID,st.ID,ss.ID DDID,ss.SENDTIME,ss.BUSINESSID  
            FROM SYNC_TABLECHANGERECORD st 
            LEFT JOIN SYNC_SENDTOAPPID ss ON ss.PARENTID =st.ID 
            LEFT JOIN CONTAINERCURRENTINFO cci ON cci.CONTAINERID =st.UNIQUECOLVALUE 
            WHERE st.TABLENAME =UPPER('CONTAINERCURRENTINFO') ";
        if (!string.IsNullOrWhiteSpace(para["containerOrProduct"])) {
            selectSQL += string.Format(" and (cci.PRODUCTNAME like '%{0}%' or cci.CONTAINERNAME like '%{0}%')", para["containerOrProduct"]);
        }
        if (!string.IsNullOrWhiteSpace(para["toappid"])) {
            selectSQL += string.Format(" and ss.TOAPPID='{0}'", para["toappid"]);
        }
        if (!string.IsNullOrWhiteSpace(para["startDate"]))
        {
            selectSQL += string.Format(" and to_char(ss.SENDTIME,'yyyy-MM-dd')>='{0}'", para["startDate"]);
        }
        if (!string.IsNullOrWhiteSpace(para["endDate"]))
        {
            selectSQL += string.Format(" and to_char(ss.SENDTIME,'yyyy-MM-dd')<='{0}'", para["endDate"]);
        }
        
        selectSQL += "ORDER BY st.CREATETIME DESC";
        var paraPage = new uMESPagingDataDTO();
        paraPage.CurrentPageIndex = Convert.ToInt32(pageNumber);
        paraPage.PageSize = Convert.ToInt32(pageSize);
        paraPage.strSQL = string.Format(selectSQL);
        uMESProcessResult result = DatabaseHelper.RunProcedureForPage(paraPage);
        context.Response.Write(JsonConvert.SerializeObject(result));
    }
    /// <summary>
    /// 查询推送产线记录--子关联明细表
    /// </summary>
    /// <param name="context"></param>
    private void getSyncTablePushRecordDetail(HttpContext context) 
    {
        var para = JsonConvert.DeserializeObject<Dictionary<string, string>>(context.Request["data"].ToString());
        uMESProcessResult result = new uMESProcessResult();
        var workflowSQL = @"select ws.workflowid,ws.workflowstepname,sc.specno,ss.toappid,ss.businessid,ss.sendtime,
            sob.stepinfoname,so.stepno,so.stepseq,so.stepinfoid,ws.workflowstepid
            from sync_tablechangerecord st 
            left join sync_sendtoappid ss on ss.parentid =st.id
            inner join workflowstep ws on ws.workflowstepid =st.uniquecolvalue 
            left join specbase sb on sb.specbaseid = ws.specbaseid
            left join spec sc on sc.specid = nvl(sb.revofrcdid,ws.specid)
            left join stepinfo so on so.workflowstepid=ws.workflowstepid
            left join stepinfobase sob on sob.revofrcdid =so.stepinfoid
            where st.tablename =upper('workflowstep')
            and ws.workflowid ='{0}' and ss.toappid='{1}'
            order by ss.toappid,sc.specno,so.stepseq ";
        DataTable workflowDT = DatabaseHelper.Query_ReturnDatTable(string.Format(workflowSQL, para["WORKFLOWID"],para["TOAPPID"]));
        workflowDT.TableName = "workflowDT";
        string stepids = string.Join(",", workflowDT.AsEnumerable().Select(row => row["stepinfoid"].ToString()));
        var stepinfoSQL = string.Format(@"select ws.workflowstepname ,sob.stepinfoname,sit.stepinfoid,
                        nvl(mt.measuretoolname,nvl(st.specialtoolname,nvl(ct.cuttoolname,ax.description))) name,
                        nvl(mt.drawingno,nvl(st.drawingno,nvl(ct.drawingno,ax.auxmaterialname))) drawingno,
                        ss.businessid,ss.sendtime,sit.qty 
                        from stepinfotools sit 
                        left join measuretool mt on mt.measuretoolid = sit.tools
                        left join specialtool st on st.specialtoolid = sit.tools
                        left join cuttool ct on ct.cuttoolid = sit.tools
                        left join auxmaterial ax on ax.auxmaterialid = sit.tools
                        left join stepinfobase sob on sob.revofrcdid =sit.stepinfoid
                        left join stepinfo so on so.stepinfoid=sob.revofrcdid
                        inner join workflowstep ws on ws.workflowstepid=so.workflowstepid
                        left join sync_tablechangerecord st2 on st2.uniquecolvalue=nvl(mt.measuretoolid,nvl(st.specialtoolid,nvl(ct.cuttoolid,ax.auxmaterialid))) 
                        left join sync_sendtoappid ss on ss.parentid =st2.id and ss.toappid ='{1}'
                        where sit.stepinfoid in ('{0}')", stepids.Replace(",", "','"), para["TOAPPID"]);
        DataTable stepinfoDT = DatabaseHelper.Query_ReturnDatTable(stepinfoSQL);
        stepinfoDT.TableName = "stepinfoDT";
        var workflowstepids = string.Join(",", workflowDT.AsEnumerable().Select(row => row["workflowstepid"].ToString()).Distinct());
        var checkpointSQL = string.Format(@" select s.stepcheckpointid,ws.workflowstepname,s.checkpointnotedis,s.drawingrequiredis,s.checkno, ss.businessid,ss.sendtime,
                         sr.workcheckpointresultnotedis,sr.checkpointresultnotedis,sr.checkdate, e.fullname 
                         from workflowstep ws
                         left join stepcheckpoint s on s.workflowstepid =ws.workflowstepid
                         left join stepcheckpointresult sr on sr.stepcheckpointid =s.stepcheckpointid and sr.containerid='{2}'
                         left join employee e on e.employeeid =sr.checkerid 
                         left join sync_tablechangerecord st2 on st2.uniquecolvalue=s.stepcheckpointid 
                         left join sync_sendtoappid ss on ss.parentid =st2.id and ss.toappid ='{1}'
                         where ws.workflowstepid in ('{0}')", workflowstepids.Replace(",", "','"), para["TOAPPID"],para["CONTAINERID"]);
        DataTable checkpointDT = DatabaseHelper.Query_ReturnDatTable(checkpointSQL);
        checkpointDT.TableName = "checkpointDT";

        var workreportSQL = string.Format(@"select ws.workflowstepname,e.fullname,c.createdate from containerstepworkreport c 
                         left join employee e on e.employeeid =c.workerid 
                         left join workflowstep ws on ws.workflowstepid =c.workflowstepid 
                         where c.containerid ='{0}' and c.workflowstepid in('{1}')", para["CONTAINERID"], workflowstepids.Replace(",", "','"));
        DataTable workreportDT = DatabaseHelper.Query_ReturnDatTable(workreportSQL);
        workreportDT.TableName = "workreportDT";

        var bomSQL = string.Format(@"SELECT pb.productname,pm.productmateriallistitemid,p.productid,pb.productbaseid,b.bomid,bb.bombaseid,sb.specname,s.specno,ST.SPECTYPENAME,pb.productname||':'||p.productrevision productinfo,s.specrevision,
                pm.materialtxnlogic IssueControl,pm.referencedesignator referencedes,pm.qtyrequired,pm.qty2required,p.productrevision,p.description pdesc,
                B.NOTES,BB.BOMNAME,PM.STEPINFOID,SIOB.STEPINFONAME,PM.BIGSMALLPART,sio.stepno||'-'||sio.description stepinfodesc,pm.productmateriallistitemname,b.bomrevision
                FROM productmateriallistitem pm
                LEFT JOIN productbase pb1 ON pb1.productbaseid=pm.productbaseid  
                LEFT JOIN product p ON p.productid=NVL(pb1.revofrcdid,pm.productid) 
                LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid 
                LEFT JOIN specbase sb1 ON sb1.specbaseid=pm.specbaseid
                LEFT JOIN spec s ON s.specid=NVL(sb1.revofrcdid,pm.specid) 
                LEFT JOIN specbase sb ON sb.specbaseid=s.specbaseid 
                LEFT JOIN BOM B ON B.BOMID = PM.BOMID
                LEFT JOIN BOMBASE BB ON BB.BOMBASEID = B.BOMBASEID
                LEFT JOIN STEPINFO SIO ON SIO.STEPINFOID = PM.STEPINFOID
                LEFT JOIN WORKFLOWSTEP WS ON SIO.Workflowstepid = WS.Workflowstepid
                LEFT JOIN STEPINFOBASE SIOB ON SIOB.STEPINFOBASEID = SIO.STEPINFOBASEID
                LEFT JOIN SPECTYPE ST ON ST.SPECTYPEID = SIO.SPECTYPEID 
                WHERE pm.bomid='{0}' 
                ORDER BY WS.SEQUENCE,sio.STEPNO,sio.STEPSEQ ", para["BOMID"]);
        DataTable bomDT = DatabaseHelper.Query_ReturnDatTable(bomSQL);
        bomDT.TableName = "bomDT";
        
        DataSet ds = new DataSet();
        ds.Tables.Add(workflowDT);
        ds.Tables.Add(stepinfoDT);
        ds.Tables.Add(checkpointDT);
        ds.Tables.Add(workreportDT);
        ds.Tables.Add(bomDT);
        result.DBDataSet = ds;
        result.Result = true;
        context.Response.Write(JsonConvert.SerializeObject(result));
    }
    /// <summary>
    /// //推送产线记录---修改重推
    /// </summary>
    private void sendAginRecord(HttpContext context)
    {
        uMESProcessResult result = new uMESProcessResult();
        var mdt = JsonConvert.DeserializeObject<Dictionary<string,string>>(context.Request["mdata"].ToString());
        var dt = JsonConvert.DeserializeObject<DataTable>(context.Request["data"].ToString());
        var index = context.Request["index"].ToString();
        
        var selectSQL = @" SELECT ss.ID FROM SYNC_TABLECHANGERECORD st 
				        LEFT JOIN SYNC_SENDTOAPPID ss ON ss.PARENTID =st.ID 
				        WHERE st.UNIQUECOLVALUE IN ('{0}') and ss.TOAPPID='{1}'";
        var arrSqlList = new ArrayList();
        if (string.IsNullOrWhiteSpace(index))
        {
            //批次信息
            getArrSqlList(selectSQL, mdt["CONTAINERID"], mdt["TOAPPID"], ref arrSqlList);
            //订单信息
            getArrSqlList(selectSQL, mdt["MFGORDERID"], mdt["TOAPPID"], ref arrSqlList);
            //批次派工表
            var cdsSql = @"select ss.id from sync_tablechangerecord st 
                 left join sync_sendtoappid ss on ss.parentid =st.id
                 left join containerstepdispatch cs on cs.containerstepdispatchid =st.uniquecolvalue 
                 where st.tablename =upper('containerstepdispatch') and ss.toappid='{1}' and cs.containerid ='{0}'";
            getArrSqlList(cdsSql, mdt["CONTAINERID"], mdt["TOAPPID"], ref arrSqlList);
            //排产明细表
            var apsSql = @"select ss.id from sync_tablechangerecord st 
                 left join sync_sendtoappid ss on ss.parentid =st.id
                 left join apsplandetail cs on cs.apsplandetailid =st.uniquecolvalue 
                 where st.tablename =upper('apsplandetail') and ss.toappid='{0}' and cs.containerid ='{1}'";
            getArrSqlList(apsSql, mdt["CONTAINERID"], mdt["TOAPPID"], ref arrSqlList);
            //工艺、工序信息
            getWorkFlowArrSqlList(selectSQL, null, mdt["WORKFLOWID"], mdt["TOAPPID"], ref arrSqlList);
            //Bom 信息
            DataTable bomDT = getBomData(mdt["BOMID"]);
            getBomArrSqlList(selectSQL, bomDT, mdt["TOAPPID"], ref arrSqlList); 
        }
        else { 
            if(index.Equals("0")){
                //工艺、工序信息
                var workflowstepids = string.Join(",", dt.AsEnumerable().Select(row => row["workflowstepid"].ToString()).Distinct());
                getWorkFlowArrSqlList(selectSQL, workflowstepids, mdt["WORKFLOWID"], mdt["TOAPPID"], ref arrSqlList);
            }
            else if (index.Equals("1")) {
                //工装工具信息
                var stepinfoid = string.Join(",", dt.AsEnumerable().Select(row => row["stepinfoid"].ToString()).Distinct());
                getToolArrSqlList(selectSQL, stepinfoid, mdt["TOAPPID"], ref arrSqlList);
            }
            else if (index.Equals("2")) {
                //检测项信息
                var stepcheckpointid = string.Join(",", dt.AsEnumerable().Select(row => row["stepcheckpointid"].ToString()).Distinct());
                getArrSqlList(selectSQL, stepcheckpointid, mdt["TOAPPID"], ref arrSqlList);
            }
            else if (index.Equals("4")) { 
                //bom 信息
                getBomArrSqlList(selectSQL, dt,mdt["TOAPPID"], ref arrSqlList); 
            }
        }
        if (arrSqlList.Count>0)
        {
            result.Result = DatabaseHelper.ExecuteSqlListTran(arrSqlList);
        }
        result.Message = result.Result ? "操作成功" : "操作失败";
        context.Response.Write(JsonConvert.SerializeObject(result));
    }

    private void getWorkFlowArrSqlList(string selectSQL, string workflowstepids, string workflowids, string toAppid, ref ArrayList arrSqlList)
    {
        DataTable WT = getWorkflowData(workflowstepids, workflowids, toAppid);
        var workflowbaseid = string.Join(",", WT.AsEnumerable().Select(row => row["workflowbaseid"].ToString()).Distinct());
        var workflowid = string.Join(",", WT.AsEnumerable().Select(row => row["workflowid"].ToString()).Distinct());
        var workflowstepid = string.Join(",", WT.AsEnumerable().Select(row => row["workflowstepid"].ToString()).Distinct());
        var specbaseid = string.Join(",", WT.AsEnumerable().Select(row => row["specbaseid"].ToString()).Distinct());
        var specid = string.Join(",", WT.AsEnumerable().Select(row => row["specid"].ToString()).Distinct());
        var stepcheckpointid = string.Join(",", WT.AsEnumerable().Select(row => row["stepcheckpointid"].ToString()).Distinct());
        var spectypeid = string.Join(",", WT.AsEnumerable().Select(row => row["spectypeid"].ToString()).Distinct());
        var objectdocinfoid = string.Join(",", WT.AsEnumerable().Select(row => row["objectdocinfoid"].ToString()).Distinct());
        getArrSqlList(selectSQL, workflowbaseid, toAppid, ref arrSqlList);
        getArrSqlList(selectSQL, workflowid, toAppid, ref arrSqlList);
        getArrSqlList(selectSQL, workflowstepid, toAppid, ref arrSqlList);
        getArrSqlList(selectSQL, specbaseid, toAppid, ref arrSqlList);
        getArrSqlList(selectSQL, specid, toAppid, ref arrSqlList);
        getArrSqlList(selectSQL, specid, toAppid, ref arrSqlList);
        getArrSqlList(selectSQL, objectdocinfoid, toAppid, ref arrSqlList);
        if (string.IsNullOrWhiteSpace(workflowstepids)) {
            getArrSqlList(selectSQL, stepcheckpointid, toAppid, ref arrSqlList);
        }
        //工序下---工步信息
        DataTable stepinfoDT = getStepInfoData(workflowstepid,toAppid);
        var stepinfoid = string.Join(",", stepinfoDT.AsEnumerable().Select(row => row["stepinfoid"].ToString()).Distinct());
        var stepinfobaseid = string.Join(",", stepinfoDT.AsEnumerable().Select(row => row["stepinfobaseid"].ToString()).Distinct());
        var spectypeid2 = string.Join(",", stepinfoDT.AsEnumerable().Select(row => row["spectypeid"].ToString()).Distinct());
        getArrSqlList(selectSQL, stepinfoid, toAppid, ref arrSqlList);
        getArrSqlList(selectSQL, stepinfobaseid, toAppid, ref arrSqlList);
        getArrSqlList(selectSQL, spectypeid2, toAppid, ref arrSqlList);
        //工步下工装信息
        if (string.IsNullOrWhiteSpace(workflowstepids)) {
            getToolArrSqlList(selectSQL, stepinfoid, toAppid, ref arrSqlList);
        }
    }

    private void getToolArrSqlList(string selectSQL, string stepids, string toAppid,ref ArrayList arrSqlList)
    {
        DataTable toolDT = getWorkflowStepTools(stepids, toAppid);
        var stepinfotoolsid = string.Join(",", toolDT.AsEnumerable().Select(row => row["stepinfotoolsid"].ToString()).Distinct());
        var measuretoolid = string.Join(",", toolDT.AsEnumerable().Select(row => row["measuretoolid"].ToString()).Distinct());
        var specialtoolid = string.Join(",", toolDT.AsEnumerable().Select(row => row["specialtoolid"].ToString()).Distinct());
        var cuttoolid = string.Join(",", toolDT.AsEnumerable().Select(row => row["cuttoolid"].ToString()).Distinct());
        var auxmaterialid = string.Join(",", toolDT.AsEnumerable().Select(row => row["auxmaterialid"].ToString()).Distinct());
        getArrSqlList(selectSQL, stepinfotoolsid, toAppid, ref arrSqlList);
        getArrSqlList(selectSQL, measuretoolid, toAppid, ref arrSqlList);
        getArrSqlList(selectSQL, specialtoolid, toAppid, ref arrSqlList);
        getArrSqlList(selectSQL, cuttoolid, toAppid, ref arrSqlList);
        getArrSqlList(selectSQL, auxmaterialid, toAppid, ref arrSqlList);
    }
    private void getBomArrSqlList(string selectSQL, DataTable dt, string toAppid, ref ArrayList arrSqlList) 
    {
        var productmateriallistitemid = string.Join(",", dt.AsEnumerable().Select(row => row["productmateriallistitemid"].ToString()).Distinct());
        var productid = string.Join(",", dt.AsEnumerable().Select(row => row["productid"].ToString()).Distinct());
        var productbaseid = string.Join(",", dt.AsEnumerable().Select(row => row["productbaseid"].ToString()).Distinct());
        var bomid = string.Join(",", dt.AsEnumerable().Select(row => row["bomid"].ToString()).Distinct());
        var bombaseid = string.Join(",", dt.AsEnumerable().Select(row => row["bombaseid"].ToString()).Distinct());
        getArrSqlList(selectSQL, productmateriallistitemid, toAppid, ref arrSqlList);
        getArrSqlList(selectSQL, productid, toAppid, ref arrSqlList);
        getArrSqlList(selectSQL, productbaseid, toAppid, ref arrSqlList);
        getArrSqlList(selectSQL, bomid, toAppid, ref arrSqlList);
        getArrSqlList(selectSQL, bombaseid, toAppid, ref arrSqlList);
    }
    

    private void getArrSqlList(string selectSql, string uniquecolvalue,string toAppid,ref ArrayList arrSqlList)
    {
        var updateSQL = @"update sync_sendtoappid set ismeshandle=0 where id in ('{0}')";
        DataTable dt = DatabaseHelper.Query_ReturnDatTable(string.Format(selectSql, uniquecolvalue.Replace(",", "','"), toAppid));
        if (dt.Rows.Count > 0)
        {
            arrSqlList.Add(string.Format(updateSQL, string.Join(",", dt.AsEnumerable().Select(row => row["ID"].ToString()).Distinct()).Replace(",", "','")));
        }
    }

    private DataTable getWorkflowData(string workflowstepids,string workflowid,string toappid)
    {
        string sql = @"select ws.workflowstepid,w.workflowid,w.workflowbaseid,sc.specbaseid,sc.specid,s.stepcheckpointid,sc.spectypeid,o.objectdocinfoid 
                        from workflowstep ws 
                        left join workflow w on w.workflowid =ws.workflowid 
                        left join specbase sb on sb.specbaseid = ws.specbaseid
                        left join spec sc on sc.specid = nvl(sb.revofrcdid,ws.specid)
                        left join stepcheckpoint s on s.workflowstepid =ws.workflowstepid
                        left join objectdocinfo o on (o.parentid =ws.workflowid or o.parentid=sc.specid) 
                        where 1=1 ";
        if (!string.IsNullOrWhiteSpace(workflowstepids)) {sql+=string.Format(" and ws.workflowstepid in('{0}')",workflowstepids.Replace(",","','")); }
        if (!string.IsNullOrWhiteSpace(workflowid)) {
            sql += string.Format(@" and ws.workflowid='{0}' 
                 and exists (select 1 from sync_tablechangerecord st 
                 left join sync_sendtoappid ss on ss.parentid =st.id
                 where st.tablename =upper('workflowstep') and st.uniquecolvalue=ws.workflowstepid and ss.toappid='{1}'
                )", workflowid,toappid);
        }
        return DatabaseHelper.Query_ReturnDatTable(sql);
    }

    private DataTable getStepInfoData(string workflowstepids,string toAppid)
    {
        string sql = @"select s.stepinfoid,sib.stepinfobaseid,s.spectypeid 
                        from  stepinfo s 
                        inner join stepinfobase sib on sib.revofrcdid = s.stepinfoid --取最新版
                        where s.workflowstepid in ('{0}')
                        and exists (select 1 from sync_tablechangerecord st 
                 left join sync_sendtoappid ss on ss.parentid =st.id
                 where st.tablename =upper('stepinfo') and st.uniquecolvalue=s.stepinfoid and ss.toappid='{1}'
                )";
        return DatabaseHelper.Query_ReturnDatTable(string.Format(sql, workflowstepids.Replace(",", "','"),toAppid));
    }

    private DataTable getWorkflowStepTools(string stepids,string toAppid)
    {
        string sql = string.Format(@"SELECT sit.stepinfotoolsid,mt.measuretoolid,st.specialtoolid,ct.cuttoolid,ax.auxmaterialid
                        FROM stepinfotools sit 
                        LEFT JOIN measuretool mt ON mt.measuretoolid = sit.tools
                        LEFT JOIN specialtool st ON st.specialtoolid = sit.tools
                        LEFT JOIN cuttool ct ON ct.cuttoolid = sit.tools
                        LEFT JOIN auxmaterial ax ON ax.auxmaterialid = sit.tools
                        WHERE sit.stepinfoid IN ('{0}')
                        and exists (select 1 from sync_tablechangerecord st 
                                 left join sync_sendtoappid ss on ss.parentid =st.id
                                 where st.tablename =upper('stepinfotools') and st.uniquecolvalue=sit.stepinfotoolsid and ss.toappid='{1}'
                                )", stepids.Replace(",", "','"),toAppid);
        return DatabaseHelper.Query_ReturnDatTable(sql);
    }
    private DataTable getBomData(string bomid)
    {
        var sql = @"select pm.productmateriallistitemid,p.productid,pb.productbaseid,b.bomid,bb.bombaseid 
                    from productmateriallistitem pm
                    left join productbase pb1 on pb1.productbaseid=pm.productbaseid  
                    left join product p on p.productid=nvl(pb1.revofrcdid,pm.productid) 
                    left join productbase pb on pb.productbaseid=p.productbaseid 
                    left join bom b on b.bomid = pm.bomid
                    left join bombase bb on bb.bombaseid = b.bombaseid 
                    where pm.bomid='{0}'";
        return DatabaseHelper.Query_ReturnDatTable(string.Format(sql, bomid));
    }

    private void sendAginEmployeeData(HttpContext context) 
    {
        var dt = JsonConvert.DeserializeObject<DataTable>(context.Request["data"].ToString());
        var toAppid = context.Request["toappid"].ToString();
        var selectSQL = @"select st.id,ss.id sendid from sync_tablechangerecord st 
            left join sync_sendtoappid ss on ss.parentid =st.id 
            where st.tablename =upper('employee') and st.uniquecolvalue ='{0}' and ss.toappid ='{1}'";
        var insertTableChangeRecord = @"insert into sync_tablechangerecord(id,tablename,uniquecolvalue,uniquecolname,operatetype,createtime)
            values('{0}','EMPLOYEE','{1}','EMPLOYEEID',0,sysdate)";
        var insertSendToappid = @"insert into sync_sendtoappid(id,parentid,toappid,ismeshandle)values(sys_guid(),'{0}','{1}',0)";
        var updateSendToappid = @"update sync_sendtoappid set ismeshandle=0 where id='{0}'";
        var arraySQL = new ArrayList();
        foreach(DataRow row in dt.Rows)
        {
            var uuid=Guid.NewGuid().ToString();
            DataTable TCR = DatabaseHelper.Query_ReturnDatTable(string.Format(selectSQL, row["employeeid"], toAppid));
            if (TCR.Rows.Count > 0) {
                if (!string.IsNullOrWhiteSpace(TCR.Rows[0]["sendid"].ToString()))
                {
                    arraySQL.Add(string.Format(updateSendToappid, TCR.Rows[0]["sendid"].ToString()));
                }
                else {
                    arraySQL.Add(string.Format(insertSendToappid, TCR.Rows[0]["id"].ToString(), toAppid));
                }
            } else {
                arraySQL.Add(string.Format(insertTableChangeRecord,uuid,row["employeeid"]));
                arraySQL.Add(string.Format(insertSendToappid,uuid,toAppid));
            }
        }
        
        var result = new uMESProcessResult();
        result.Result = DatabaseHelper.ExecuteSqlListTran(arraySQL);
        result.Message = result.Result ? "操作成功" : "操作失败";
        context.Response.Write(JsonConvert.SerializeObject(result));
    }
}